﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Shutterstock.Salesforce.SSISDownload.Logging;
using Shutterstock.Salesforce.Tools;

namespace Shutterstock.Salesforce.SSISDownload.SFTableOperations
{
    /// <summary>
    /// Represents a Table deleter
    /// </summary>
    public static class TableDeleter
    {
        /// <summary>
        /// Delete the deleted data from the table
        /// </summary>
        /// <param name="replicationParam"></param>
        public static void Delete(ReplicationParameter replicationParam)
        {
            using (SqlConnection Dest = new SqlConnection(replicationParam.DestConnectionString))
            {

                Dest.Open();

                SqlCommand deleteCmd = Dest.CreateCommand();
                string deleteQuery = String.Format("delete from [{0}] where Id = '{{0}}'", replicationParam.SFTableInfo.TableName);

                SqlCommand sqlCmd = Dest.CreateCommand();

                // call getdeleted api function if the table is replicateable
                if (replicationParam.SFTableInfo.Replicateable)
                {
                    // 20 min before the last created date
                    // get the timestamp column name
                    string columnName = replicationParam.SFTableInfo.GetModifiedDateField();
                    // get the most recent record from table and minus 20 min
                    sqlCmd.CommandText = string.Format("select max({0}) from [{1}]", columnName, replicationParam.SFTableInfo.TableName);
                    Object obj = sqlCmd.ExecuteScalar();
                    DateTime lastCreatedDate;
                    if (obj is DBNull || (DateTime)obj < DateTime.Today.AddDays(-29))
                        return;
                    else
                    {
                        lastCreatedDate = ((DateTime)obj).AddMinutes(-20);
                        DateTime startTime = DateTime.Now;
                        List<string> deletedIDs = replicationParam.SalesForceConnection.SalesForceService.GetDeletedIDs(replicationParam.SFTableInfo.TableName, lastCreatedDate, DateTime.Now);

                        
                        DataTable dt = new DataTable();
                        dt.Columns.Add(new DataColumn("id",typeof(string)));

                        foreach(string id in deletedIDs)
                        {
                           DataRow row =  dt.NewRow();
                           row[0] = id;
                           dt.Rows.Add(row);
                        }


                        sqlCmd.CommandText = replicationParam.SFTableInfo.GenerateCreateTempTableForDelete();
                        sqlCmd.ExecuteNonQuery();

                        using (SqlBulkCopy copy = new SqlBulkCopy(Dest))
                        {
                            copy.DestinationTableName = replicationParam.SFTableInfo.GetDeleteTableName();
                            copy.WriteToServer(dt);
                        }

                        sqlCmd.CommandText = replicationParam.SFTableInfo.CreateIDIndexForDeleteTable(replicationParam.SFTableInfo.GetDeleteTableName());
                        sqlCmd.ExecuteNonQuery();

                        
                        Logger.Log(FunctionType.WebServiceGetDeletedIDs, replicationParam.SFTableInfo.TableName, TableTaskType.Delete, startTime, deletedIDs.Count);
                        startTime = DateTime.Now;
                        //foreach (string salesforceID in deletedIDs)
                        //{
                        //    deleteCmd.CommandText = string.Format(deleteQuery, salesforceID);
                        //    deleteCmd.ExecuteNonQuery();
                        //}

                        deleteCmd.CommandText = String.Format("Delete from [{0}] From [{0}] , [{1}] where [{0}].id = [{1}].id", replicationParam.SFTableInfo.TableName, replicationParam.SFTableInfo.GetDeleteTableName());
                        deleteCmd.ExecuteNonQuery();

                        StringBuilder drop = new StringBuilder();
                        drop.AppendLine(String.Format(" IF object_id('{0}') is not null", replicationParam.SFTableInfo.GetDeleteTableName()));
                        drop.AppendLine(" begin");
                        drop.AppendLine(String.Format(" Drop Table [{0}] ", replicationParam.SFTableInfo.GetDeleteTableName()));
                        drop.AppendLine(" end");
                        sqlCmd.CommandText = drop.ToString();
                        sqlCmd.ExecuteNonQuery();


                        if (deletedIDs.Count > 0)
                            Logger.Log(FunctionType.DeleteCmd, replicationParam.SFTableInfo.TableName, TableTaskType.Delete, startTime, deletedIDs.Count);

                    }


                    // compare the two list of ids from two tables. remove the rows in destination database which are not in the source database. 
                    // Can be necesary if deleted records are not being kept
                    if (replicationParam.ManualComparison)
                    {
                        // create a temp table to store the new list of ids from salesforce source
                        // load all the ids in the temp table
                        // do delete command. to delete anything which the id is not in the temp table. 
                        string tempName = replicationParam.SFTableInfo.GetTempTableName();
                        sqlCmd.CommandText = replicationParam.SFTableInfo.GenerateCreateTempTableForDelete();
                        sqlCmd.ExecuteNonQuery();


                        string sql = String.Format("Select Id from [{0}]", replicationParam.SFTableInfo.TableName);

                        DateTime start = DateTime.Now;
                        DataTable dataTable = replicationParam.SalesForceConnection.SalesForceService.GetDataTable(sql, replicationParam.SFTableInfo);
                        Logger.Log(FunctionType.WebServiceGetTable, replicationParam.SFTableInfo.TableName, TableTaskType.Delete, start, dataTable.Rows.Count);
                        start = DateTime.Now;
                        using (SqlBulkCopy BulkDestination = new SqlBulkCopy(Dest))
                        {
                            BulkDestination.DestinationTableName = tempName;
                            BulkDestination.BulkCopyTimeout = 0;
                            BulkDestination.BatchSize = replicationParam.BatchSize;
                            BulkDestination.WriteToServer(dataTable);
                        }

                        deleteCmd.CommandText = replicationParam.SFTableInfo.CreateIDIndexForDeleteTable(tempName);
                        deleteCmd.ExecuteNonQuery();

                        Logger.Log(FunctionType.BulkLoad, replicationParam.SFTableInfo.TableName, TableTaskType.Delete, start, dataTable.Rows.Count);

                        start = DateTime.Now;


                        deleteCmd.CommandText = string.Format("delete from [{0}] where not exists (select 1 from [{1}] where [{0}].id = [{1}].id)", replicationParam.SFTableInfo.TableName, tempName);
                        deleteCmd.CommandTimeout = 300;
                        deleteCmd.ExecuteNonQuery();
                        Logger.Log(FunctionType.DeleteCmd, replicationParam.SFTableInfo.TableName, TableTaskType.Delete, start, dataTable.Rows.Count);
                        // remove the temp table
                        StringBuilder drop = new StringBuilder();
                        drop.AppendLine(String.Format(" IF object_id('{0}') is not null", tempName));
                        drop.AppendLine(" begin");
                        drop.AppendLine(String.Format(" Drop Table [{0}] ", tempName));
                        drop.AppendLine(" end");
                        sqlCmd.CommandText = drop.ToString();
                        sqlCmd.ExecuteNonQuery();
                    }
                }
            }
            
        }
    }
}
